﻿/*
2021/12/13 添加查询
SELECT   ACDC.Result.ID, ACDC.Program.Name AS K1001, RVS_K1002.ItemValue AS K1002, RVS_K1081.ItemValue AS K1081, 
                ACDC.Unit.Name AS K1086, PP1.ID AS K2001, PP1.LimitLow AS K2110_1, PP1.LimitHigh AS K2111_1, 
                ACDC.ResultTightening.FinalAngle, ACDC.ResultTightening.FinalTorque, PP1.ProgramParameterTypeID
FROM      ACDC.Result INNER JOIN
                ACDC.Program ON ACDC.Result.ProgramID = ACDC.Program.ID INNER JOIN
                ACDC.Unit ON ACDC.Result.UnitID = ACDC.Unit.ID INNER JOIN
                ACDC.ProgramParameter AS PP1 ON ACDC.Result.ProgramID = PP1.ProgramID INNER JOIN
                ACDC.ResultTightening ON ACDC.Result.ID = ACDC.ResultTightening.ResultID INNER JOIN
                ACDC.ResultToResultIdentifier ON ACDC.Result.ID = ACDC.ResultToResultIdentifier.ResultID INNER JOIN
                ACDC.ResultValueString AS RVS_K1002 ON ACDC.Result.ID = RVS_K1002.ResultID INNER JOIN
                ACDC.ResultValueString AS RVS_K1081 ON ACDC.Result.ID = RVS_K1081.ResultID
WHERE   (RVS_K1002.ResultValueTypeID = 95) AND (RVS_K1081.ResultValueTypeID = 89)

 */
using QDAS;
using QDasConverter.Utils;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WindGoes6.Database;

namespace QDasConverter.Core
{
    public class C2021T01_Atlas : ConvertBase
    {
        string k1303 = "";
        string k1042 = "";
        string k1091 = "";
        DateTime startDate = new DateTime(2000, 1, 1);
        Dictionary<string, string[]> programParamters = new Dictionary<string, string[]>();
        Dictionary<string, string> rsdic = new Dictionary<string, string>();
        List<QFile> qfs = new List<QFile>();
        Dictionary<string, string> K1002s = new Dictionary<string, string>();
        Dictionary<string, string> K1042s = new Dictionary<string, string>();


        public C2021T01_Atlas()
        {
            DisplayName = "柯马ATLAS转换器";
            DisplayName = "Atlas 转换器";
            TableName = "ACDC.Result";
            TableIDName = "ID";
            Version = "alpha Released Date: 2021/10/05 Inner Name: C2021T01_Atlas";
            Version = "beta1 Released Date: 2021/11/09 Inner Name: C2021T01_Atlas";
            Version = "beta1 Released Date: 2021/11/09 Inner Name: C2021T01_Atlas";
            Version = "beta0.1.0 Released Date: 2021/11/17 Inner Name: C2021T01_Atlas";
        }

        public override bool OnStart()
        {
            k1303 = ia.ReadValue("K1303");
            k1042 = ia.ReadValue("K1042");
            k1091 = ia.ReadValue("K1091");

            if (DebugMode)
            {
                SqlConnectionString = "Data Source = 117.71.62.132,8092;Initial Catalog = atlas;User ID = sa; Password = Huaun@2021;";
                sm = new WindGoes6.Database.SQLManager(SqlConnectionString);
            }

            return base.OnStart();
        }


        /// <summary>
        /// 从文件中加载字典。
        /// </summary>
        /// <param name="dic"></param>
        /// <param name="inifile"></param>
        private void LoadDictionary(Dictionary<string, string> dic, string inifile)
        {
            inifile = Path.GetFullPath(inifile);
            if (!File.Exists(inifile))
                return;

            foreach (var line in File.ReadAllLines(inifile))
            {
                if (line == null || !line.Contains("="))
                    continue;

                string[] keys = line.Split('=')[1].Split(',');
                string value = line.Split('=')[0];
                foreach (string key in keys)
                    if (!dic.Keys.Contains(key))
                        dic.Add(key, value);
            }
        }


        public override void OneLoop_Process()
        {
            string condition1 = "ResultDateTime >= '" + startDate.ToString() + "'";
            string condition2 = "ResultTightening.ResultID > " + LastResID;
            string sql = $@"SELECT TOP {BatchCount}
        ACDC.Result.ID, 
        ACDC.Program.Name AS K1001, 
        RVS_K1002.ItemValue AS K1002, 
        RVS_K1081.ItemValue AS K1081, 
        ACDC.Unit.Name AS K1086, 
        PP1.ID AS K2001, 
        PP1.LimitLow AS K2110, 
        PP1.LimitHigh AS K2111, 
        PP1.ProgramParameterTypeID AS Pid,
        ACDC.ResultTightening.FinalTorque, 
        ACDC.ResultTightening.FinalAngle,
		ACDC.Result.ResultDateTime as K0004, 
		ACDC.ResultIdentifier.Identifier as K0014
FROM    ACDC.Result INNER JOIN
        ACDC.Program ON ACDC.Result.ProgramID = ACDC.Program.ID INNER JOIN
        ACDC.Unit ON ACDC.Result.UnitID = ACDC.Unit.ID INNER JOIN
        ACDC.ProgramParameter AS PP1 ON ACDC.Result.ProgramID = PP1.ProgramID INNER JOIN
        ACDC.ResultTightening ON ACDC.Result.ID = ACDC.ResultTightening.ResultID INNER JOIN
        ACDC.ResultToResultIdentifier ON ACDC.Result.ID = ACDC.ResultToResultIdentifier.ResultID INNER JOIN
        ACDC.ResultValueString AS RVS_K1002 ON ACDC.Result.ID = RVS_K1002.ResultID INNER JOIN
        ACDC.ResultValueString AS RVS_K1081 ON ACDC.Result.ID = RVS_K1081.ResultID INNER JOIN
        ACDC.ResultIdentifier ON ACDC.ResultToResultIdentifier.ResultIdentifierID = ACDC.ResultIdentifier.ID
WHERE   (RVS_K1002.ResultValueTypeID = 95) AND (RVS_K1081.ResultValueTypeID = 89) and ACDC.Result.ID > {LastResID}";
            sm.CommandText = sql;


            string[][] data = sm.GetStrings();
            if (data == null || data.Length == 0)
                return;

            qfs.Clear();
            foreach (string[] item in data)
            {
                try
                {
                    processItem(item);
                }
                catch (Exception ex)
                {
                    AddLog("DEBUG", "transStep.4:" + ex.Message);
                }
            }

            foreach (QFile qf in qfs)
            {
                SaveToDFQ(qf, $"{OutputDirectory}\\atlas_{qf.Tag}_{DateTime.Now:yyyyMMddHHmmssfff}.dfq");
            }
        }

        public override void OneLoop_Completed()
        {
            LastResID++;
        }

        public override void OnStopped()
        {
            print("Stopped.");
        }

        public void AddLog(string level, string log)
        {
            Common.AddLog(level + "\t" + log);
        }

        public override bool IsLoopCompleted()
        {
            //LastResID = ia.ReadInt("LastResID", LastResID);
            //startDate = ia.ReadDateTime("StartTime", new DateTime(2020, 1, 1));
            //sm.CommandText = "select count(ID) from Result where ";
            //sm.CommandText += LastResID == 0 ? $"ResultDateTime >= '{startDate}'" : $"ID > {LastResID}";
            //object res = sm.GetObject();
            //return res != null && int.TryParse(res.ToString(), out int newid) && newid > BatchCount;
            return false;
        }


        private void processItem(string[] item)
        {
            /** 2021/12/13 完全重写
             * 00. ACDC.Result.ID, 
             * 01. ACDC.Program.Name AS          K1001, 
             * 02. RVS_K1002.ItemValue AS        K1002, Bolt01
             * 03. RVS_K1081.ItemValue AS        K1081, 
             * 04. ACDC.Unit.Name AS             K1086, 
             * 05. PP1.ID AS                     K2001, 
             * 06. PP1.LimitLow AS               K2110, 
             * 07. PP1.LimitHigh AS              K2111, 
             * 08. PP1.ProgramParameterTypeID AS Pid,
             * 09. ACDC.ResultTightening.FinalTorque, 
             * 10. ACDC.ResultTightening.FinalAngle
             * 11. ACDC.Result.ResultDateTime as K0004
             * 12. ACDC.ResultIdentifier.Identifier as K0014
             ***************************************/

            // 去除字符两端多余空格
            for (int i = 0; i < item.Length; i++)
                if(!string.IsNullOrEmpty(item[i]))
                    item[i] = item[i].Trim();
            
            string resultID = item[0];
            string pid = item[8];
            bool isTorque = pid.Contains("1");
            string k1001 = item[1];
            string k1002 = item[2];
            string k1081 = item[3];
            string k1086 = item[4];
            string k2001 = item[5];
            string k2002 = isTorque ? "FinalTorque" : "FinalAngle";
            string k2110 = item[6];
            string k2111 = item[7];
            string finalTorque = item[9]; // FinalTorque
            string finalAngle = item[10]; // FinalAngle
            DateTime dt = DateTime.Parse(item[11]);
            string k0014 = item[12];

            string tag = $"{k1001}_{k1002}";
            QFile qf = qfs.Find(q => (q.Tag as string) == tag);
            if (qf == null)
            {
                qf = new QFile();
                qfs.Add(qf);
                qf[1001] = k1001;
                qf[1002] = k1002;
                qf[1042] = k1042;
                qf[1081] = k1081;
                qf[1086] = k1086;
                qf.Tag = $"{k1001}_{k1002}";
            }

            QCharacteristic qc = GetCharacteristic(qf, k2001);
            qc[2001] = k2001;
            qc[2002] = isTorque ? "FinalTorque" : "FinalAngle";
            qc[2110] = k2110;
            qc[2111] = k2111;
            qc[2142] = isTorque ? "Nm" : "°";

            QDataItem qv1 = qc.AddItem();
            qv1.SetValue(isTorque ? finalTorque : finalAngle);
            qv1.date = dt;
            qv1[0014] = k0014;
            qv1[0010] = 1103030;


        }

        /// <summary>
        /// 2019/01/15，根据《柯马ATLAS转换器开发说明书20190110.docx》中的EXCEL附件的要求修改。
        /// </summary>
        /// <param name="identifier">抽样值。</param>
        /// <returns></returns>
        private string GetK1001(string identifier)
        {
            string k1001 = null;
            string id = identifier.Trim();

            // 1.发动机 提取第一个*之前的内容 不定长, 如 JLH-4G20TD*K1T0000024S*  =>  JLH-4G20TD
            if (identifier.Contains('*'))
            {
                k1001 = identifier.Split('*')[0];
            }

            // 2. 提取以1242或1243开始的前四位并在前面加 550000，如 1242#4D19007#1W000012#T1#  =>  5500001242
            if (id.StartsWith("1242") || id.StartsWith("1243"))
            {
                k1001 = "550000" + id.Substring(0, 4);
            }
            // 第一位固定值为P时，第十二位固定值为#时，提取2-11位	32
            else if (id.Length == 32 && id.StartsWith("P") && id[11] == '#')
            {
                k1001 = id.Substring(1, 10);
            }

            // 3. 涡轮增压器
            // 前十位全为阿拉伯数字时, 提取第1 - 10位，25
            else if (id.Length == 25 && Common.IsNumber(id, 0, 10))
            {
                k1001 = id.Substring(0, 10);
            }
            // 提取第1-8位	31
            else if (id.Length == 31)
            {
                k1001 = id.Substring(0, 8);
            }
            // 提取第1-10位	33
            else if (id.Length == 33)
            {
                k1001 = id.Substring(0, 10);
            }


            // 4.进气歧管组件
            // 第一位固定值为P时，第十二位固定值为#时，提取2-11位	25
            else if (id.Length == 25 && id.StartsWith("P") && id[11] == '#')
            {
                k1001 = id.Substring(1, 10);
            }
            // 第一位固定值为P时，第十位固定值为#时，提取2-9位	30
            else if (id.Length == 25 && id.StartsWith("P") && id[9] == '#')
            {
                k1001 = id.Substring(1, 8);
            }

            // 5.缸盖 末位固定值为#时，倒数第十二位固定值为P时，提取15-24位
            // 末位固定值为#时，倒数第十二位固定值为P时，提取15-24位	25
            else if (id.Length == 25 && id.EndsWith("#") && id[id.Length - 12] == 'P')
            {
                k1001 = identifier.Substring(14, 10);
            }

            // 6 缸体
            // 末位固定值为#时，倒数第十二位固定值为P时，提取第13-22位	23
            else if (id.Length == 23 && id[id.Length - 1] == 'P')
            {
                k1001 = id.Substring(12, 10);
            }
            // 第十二位固定值为P时，第二十三位固定值为#时提取第13-22位	32
            else if (id.Length == 32 && id[22] == 'P')
            {
                k1001 = id.Substring(12, 10);
            }

            return k1001;
        }



        private void prepareDictionaries()
        {
            /*************************************************************************************************
			 *  Program Parameter Data
			 *  0.ID	1.ProgramID	2.ProgramParameterTypeID	3.ParameterValue	4.LimitHigh	5.LimitLow
			 *  e.g.  1   1   1   NULL    27  23
			 **************************************************************************************************/
            sm.CommandText = "select * from ProgramParameter";
            string[][] pps = sm.GetStrings(); // PPS is ProgramParameter. 

            programParamters.Clear();
            for (int i = 0; i < pps.Length; i++)
                programParamters.Add(pps[i][1] + "_" + pps[i][2], pps[i]);

            /*************************************************************************************************
			 *  Result Step Data
			 *  0.ResultID, 1.ResultStepID. 
			 **************************************************************************************************/
            sm.CommandText = "select ResultID, ResultStepID from ResultStep";
            string[][] rss = sm.GetStrings();
            //AddLog("DEBUG", "rss.count = " + rss.Length);

            rsdic.Clear();
            for (int i = 0; i < rss.Length; i++)
                if (!rsdic.Keys.Contains(rss[i][0]))
                    rsdic.Add(rss[i][0], rss[i][1]);
        }

        private QCharacteristic GetCharacteristic(QFile qf, string k2001)
        {

            // 找到K2001和K2002相同的参数。
            var q = qf.Charactericstics.Find(qc => qc[2001].ToString() == k2001);

            // 如果没找到则用K2001和K2002初始化一个新的参数。
            if (q == null)
            {
                q = new QCharacteristic();
                q[2001] = k2001;
                qf.Charactericstics.Add(q);
            }

            return q;
        }


        string sqltemplate =
@"SELECT TOP {count}
	ResultTightening.ResultID, 
	ResultTightening.FinalAngle, 
	ResultTightening.FinalTorque, 
	Result.ResultDateTime, 
	Result.ProgramID, 
	ResultIdentifier.Identifier,
    'UnitName' as UnitName, -- vUnitProgramBolt.UnitName,
    'BoltName' as BoltName,--vUnitProgramBolt.BoltName,
    'SystemType' as SystemType,--vUnitProgramBolt.SystemType,
    ProgramParameter.ParameterValue,
    ProgramParameter.LimitHigh,
    ProgramParameter.LimitLow,
    Result.PositionID,
    'ProgramName' as UnitName, --vUnitProgramBolt.ProgramName,
    ResultStep.ResultStepID
FROM 
	ACDC.Result INNER JOIN
	ACDC.ResultTightening ON ACDC.Result.ID = ACDC.ResultTightening.ResultID INNER JOIN
	-- vUnitProgramBolt ON ACDC.Result.ID = vUnitProgramBolt.ResultId INNER JOIN
	ACDC.ResultToResultIdentifier ON ACDC.ResultToResultIdentifier.ResultID = ACDC.Result.ID INNER JOIN
	ACDC.ResultIdentifier ON ACDC.ResultToResultIdentifier.ResultIdentifierID = ACDC.ResultIdentifier.ID INNER JOIN
	ACDC.ProgramParameter ON ACDC.Result.ProgramID = ACDC.ProgramParameter.ProgramID LEFT OUTER JOIN
    ACDC.ResultStep ON ACDC.Result.ID = ACDC.ResultStep.ResultID
WHERE 
	ACDC.ProgramParameter.ProgramParameterTypeID in (1,2)
	and 	{condition}
ORDER BY
	ACDC.ResultTightening.ResultID";
    }
}